<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>回访视图</title>
</head>
<body>
    <h1>干系人过滤（符合条件）</h1>
    <pre>
        select 
        a.phid_pc,
        project.phid_org,
        project.bill_name,
        project.provinceid AS pno,
        project.regionid AS cno,
        a.u_gxr,
        a.u_visit_time,
        a.khdwmc,
        a.schfqk,
        pd.u_zt,
        a.u_remarks,
        pd.u_gxr_xm,
        pd.u_position,
        pd.u_customer_name,
        pd.u_mobile_phone,
        pd.u_customer_name AS gxr_dwmc,
        a.rn
        from (WITH combined_data AS (
            SELECT pd1.phid_pc, 
                   pd1.u_gxr AS u_visit_ps, 
                   NVL(pd1.u_s_visit_time,TO_DATE('1970-01-01', 'YYYY-MM-DD')) AS u_visit_time, 
                   pd1.u_khdwmc AS khdwmc,
                   pd1.u_schfqk AS schfqk,
                   pd1.u_gxrzt,
                   pd1.u_remarks
            FROM p_form_telephone_plan_d pd1
        left join p_form_telephone_plan_m pm1 on pd1.pphid = pm1.phid 
        where pm1.app_status='1'    
            UNION ALL
            SELECT pd2.phid_pc, 
                   pd2.u_plan_visit_ps AS u_visit_ps, 
                   NVL(pd2.u_s_visit_time,TO_DATE('1970-01-01', 'YYYY-MM-DD')) AS u_visit_time,  --时间出现问题
                   pd2.u_khdw AS khdwmc,
                   pd2.u_schfqk AS schfqk,
                   pd2.u_gxrzt,
                   pd2.u_remarks
            FROM p_form_visit_plan_d pd2
        left join p_form_visit_plan_m pm2 on pd2.pphid=pm2.phid
        where pm2.app_status='1'
            UNION ALL
            SELECT pd3.phid_pc, 
                   pd3.u_khxm AS u_visit_ps, 
                   NVL(pd3.u_visit_time,TO_DATE('1970-01-01', 'YYYY-MM-DD')) as u_visit_time,
                   pd3.u_dwmc AS khdwmc,
                   pd3.u_hf_result AS schfqk,
                   pd3.u_gxrzt,  
                   pd3.u_remarks  
            FROM p_form_nps_visit_record_d pd3
            left join  p_form_nps_visit_record_m pm3 on pm3.phid = pd3.pphid
            where pm3.app_status='1'
            UNION ALL
            SELECT m.phid_pc as phid_pc,
                   d.phid as u_visit_ps,
                   TO_DATE('1970-01-01', 'YYYY-MM-DD') as u_visit_time,
                   NULL as khdwmc,
                   NULL as schfqk,
                   NULL as u_gxrzt,
                   NULL as u_remarks
            FROM p_form_pc_stakeholders_d d
            LEFT JOIN p_form_pc_stakeholders_m m ON d.pphid = m.phid
            where m.app_status='1'
        ),
        latest_data AS (
            SELECT phid_pc, 
                   u_visit_ps, 
                   u_visit_time, 
                   khdwmc,
                   schfqk,
                   u_gxrzt,
                   u_remarks,
                   ROW_NUMBER() OVER (PARTITION BY u_visit_ps ORDER BY u_visit_time DESC) AS rn
            FROM combined_data
        )
        SELECT d1.phid_pc, 
               d1.u_visit_ps AS u_gxr, 
               d1.u_visit_time,
               d1.khdwmc,
               d1.schfqk,
               d1.u_gxrzt,
               d1.u_remarks,
               d1.rn
        FROM latest_data d1
        WHERE (d1.u_visit_time = TO_DATE('1970-01-01', 'YYYY-MM-DD') 
           OR d1.u_visit_time >= SYSDATE - 90)
           AND d1.phid_pc IS NOT NULL
          AND NOT EXISTS (
              SELECT 1
              FROM combined_data d2
              WHERE TO_CHAR(d2.u_visit_time, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
                AND d2.phid_pc = d1.phid_pc
          )
          AND NOT EXISTS (
              SELECT 1
              FROM combined_data d3
              WHERE TO_CHAR(d3.u_visit_time, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
              AND d3.phid_pc = d1.phid_pc
              AND d3.u_visit_ps != d1.u_visit_ps
          )
        
          AND d1.rn = 1
        ORDER BY d1.phid_pc) a
        LEFT JOIN p_form_pc_stakeholders_d pd on a.u_gxr= pd.phid 
        LEFT JOIN p_form_pc_stakeholders_m pm on pd.pphid=pm.phid
        LEFT JOIN project_table project ON project.phid = a.phid_pc
        WHERE pd.u_zt = 1 and pm.app_status = '1'
    </pre>
        <h1>干系人过滤（所有有效干系人）</h1>
        <pre>
            select 
a.phid_pc,
project.phid_org,
project.bill_name,
project.provinceid AS pno,
project.regionid AS cno,
a.u_gxr,
a.u_visit_time,
a.khdwmc,
a.schfqk,
pd.u_zt,
a.u_remarks,
pd.u_gxr_xm,
pd.u_position,
pd.u_customer_name,
pd.u_mobile_phone,
pd.u_customer_name AS gxr_dwmc,
a.rn
from (WITH combined_data AS (
    SELECT pd1.phid_pc, 
           pd1.u_gxr AS u_visit_ps, 
           NVL(pd1.u_s_visit_time,TO_DATE('1970-01-01', 'YYYY-MM-DD')) AS u_visit_time, 
           pd1.u_khdwmc AS khdwmc,
           pd1.u_schfqk AS schfqk,
           pd1.u_gxrzt,
           pd1.u_remarks
    FROM p_form_telephone_plan_d pd1
left join p_form_telephone_plan_m pm1 on pd1.pphid = pm1.phid 
where pm1.app_status='1'
    UNION ALL
    SELECT pd2.phid_pc, 
           pd2.u_plan_visit_ps AS u_visit_ps, 
           NVL(pd2.u_s_visit_time,TO_DATE('1970-01-01', 'YYYY-MM-DD')) AS u_visit_time,  --时间出现问题
           pd2.u_khdw AS khdwmc,
           pd2.u_schfqk AS schfqk,
           pd2.u_gxrzt,
           pd2.u_remarks
    FROM p_form_visit_plan_d pd2
left join p_form_visit_plan_m pm2 on pd2.pphid=pm2.phid
where pm2.app_status='1'
    UNION ALL
    SELECT pd3.phid_pc, 
           pd3.u_khxm AS u_visit_ps, 
           NVL(pd3.u_visit_time,TO_DATE('1970-01-01', 'YYYY-MM-DD')) as u_visit_time,
           pd3.u_dwmc AS khdwmc,
           pd3.u_hf_result AS schfqk,
           pd3.u_gxrzt,  
           pd3.u_remarks  
    FROM p_form_nps_visit_record_d pd3
    left join  p_form_nps_visit_record_m pm3 on pm3.phid = pd3.pphid
    where pm3.app_status='1'
    UNION ALL
    SELECT m.phid_pc as phid_pc,
           d.phid as u_visit_ps,
           TO_DATE('1970-01-01', 'YYYY-MM-DD') as u_visit_time,
           NULL as khdwmc,
           NULL as schfqk,
           NULL as u_gxrzt,
           NULL as u_remarks
    FROM p_form_pc_stakeholders_d d
    LEFT JOIN p_form_pc_stakeholders_m m ON d.pphid = m.phid
    where m.app_status='1'
),
latest_data AS (
    SELECT phid_pc, 
           u_visit_ps, 
           u_visit_time, 
           khdwmc,
           schfqk,
           u_gxrzt,
           u_remarks,
           ROW_NUMBER() OVER (PARTITION BY u_visit_ps ORDER BY u_visit_time DESC) AS rn
    FROM combined_data
)
SELECT d1.phid_pc, 
       d1.u_visit_ps AS u_gxr, 
       d1.u_visit_time,
       d1.khdwmc,
       d1.schfqk,
       d1.u_gxrzt,
       d1.u_remarks,
       d1.rn
FROM latest_data d1
WHERE d1.rn = 1
ORDER BY d1.phid_pc) a
LEFT JOIN p_form_pc_stakeholders_d pd on a.u_gxr= pd.phid 
LEFT JOIN p_form_pc_stakeholders_m pm on pd.pphid=pm.phid
LEFT JOIN project_table project ON project.phid = a.phid_pc
WHERE pd.u_zt = 1 and pm.app_status = '1'
        </pre>
</body>
</html>